package org.ccu.eshop.dao;

import org.ccu.eshop.domain.dto.CartSelectedMerVo;
import org.ccu.eshop.domain.dto.CartVo;
import org.ccu.eshop.domain.dto.CategoryVO;
import org.ccu.eshop.domain.dto.OrdersVo;
import org.ccu.eshop.utils.ComboPooledDataSourceUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ShopCartDao {
    /**
     * 通过用户id来查询没有提交的购物车
     * @param member
     * @return
     */
    public CartVo selectCartByMember(Integer member) {

        CartVo cartVo=null;
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{

            //2-1获得承接sql的容器对象
            String sql="select id,member,money,cartStatus from cart where member=? and cartStatus=0";
            //2-2获得sql语句
            PreparedStatement ps=connection.prepareStatement(sql);
             ps.setInt(1,member);
            //2-3执行sql语句
            ResultSet set=ps.executeQuery();
            while (set.next()){
                    cartVo=new CartVo(set.getInt("id"),set.getInt("member"),set.getBigDecimal("money"),set.getInt("cartStatus"));
                    return cartVo;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return cartVo;
    }


    /**
     * 添加购物车
     * @param member
     * @return
     */
    public boolean insert(Integer member) {
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{
            //2-1获得承接sql的容器对象
            String sql="insert into cart(member,money,cartStatus) values(?,?,?)";
            //2-2获得sql语句
            PreparedStatement  ps=connection.prepareStatement(sql);
            ps.setInt(1,member);
            ps.setBigDecimal(2,new BigDecimal("0.00"));
            ps.setInt(3,0);
            //2-3执行sql语句
            int row=ps.executeUpdate();
            if (row>0){
              return true;
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public List<CartSelectedMerVo> selectCartSelectMerListByCar(Integer cart) {

        List<CartSelectedMerVo> cartSelectedMerVoList=new ArrayList<>();

        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{

            //2-1获得承接sql的容器对象
            String sql="SELECT c.id,c.cart,c.merchandise,c.number,c.price,c.money,m.merName merName,m.price merPrice from cartselectedmer c,merchandise m " +
                    " where cart=? and c.merchandise=m.id";
            //2-2获得sql语句
            PreparedStatement  ps=connection.prepareStatement(sql);
            ps.setInt(1,cart);
            //2-3执行sql语句
            ResultSet set=ps.executeQuery();
            while (set.next()){
                CartSelectedMerVo vo=new CartSelectedMerVo(set.getInt("id"), set.getInt("cart"), set.getInt("merchandise"),
                        set.getInt("number") ,set.getBigDecimal("price") ,
                        set.getBigDecimal("money") ,set.getString("merName") , set.getBigDecimal("merPrice"));
                cartSelectedMerVoList.add(vo);
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
            return cartSelectedMerVoList;
    }


    public boolean updateCartSelectedMernumber(Integer id,Integer cart) {

        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{
            connection.setAutoCommit(false);
            //2-1获得承接sql的容器对象
            String sql="update cartselectedmer set number =number+1,money=money+price " +
                    " where id=?";
            //2-2获得sql语句
            PreparedStatement  ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            int row1=ps.executeUpdate();

            //更新购物车
            String sql2="update cart set money=(select sum(money) from cartselectedmer where cart=? group by cart) where id=?";
            ps=connection.prepareStatement(sql2);
            ps.setInt(1,cart);
            ps.setInt(2,cart);
            //2-3执行sql语句
            int row2=ps.executeUpdate();
            if(row1>0&&row2>0){
                connection.commit();
                return true;
            }

        }catch (Exception e){
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  false;
    }

    public CartSelectedMerVo selectCartSelectedMerByCarAndMid(Integer cart, int merchandise) {
        CartSelectedMerVo vo=null;
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{

            //2-1获得承接sql的容器对象
            String sql="SELECT c.id,c.cart,c.merchandise,c.number,c.price,c.money from cartselectedmer c " +
                    " where cart=? and c.merchandise=?";
            //2-2获得sql语句
            PreparedStatement  ps=connection.prepareStatement(sql);
            ps.setInt(1,cart);
            ps.setInt(2,merchandise);
            //2-3执行sql语句
            ResultSet set=ps.executeQuery();
            if(set.next()){
                 vo=new CartSelectedMerVo(set.getInt("id"), set.getInt("cart"), set.getInt("merchandise"),
                        set.getInt("number") ,set.getBigDecimal("price") ,
                        set.getBigDecimal("money") );
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return vo;
    }

    public boolean addCartSelectedMer(Integer cart, Integer merchandise, BigDecimal price) {
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{
            connection.setAutoCommit(false);
            //2-1获得承接sql的容器对象
            String sql="insert into  cartselectedmer(cart,merchandise,number,price,money) values (?,?,1,?,?)" ;
            //2-2获得sql语句
            PreparedStatement  ps=connection.prepareStatement(sql);
            ps.setInt(1,cart);
            ps.setInt(2,merchandise);
            ps.setBigDecimal(3,price);
            ps.setBigDecimal(4,price);
            int row1=ps.executeUpdate();

            //更新购物车
            String sql2="update cart set money=(select sum(money) from cartselectedmer where cart=? group by cart) where id=?";
            ps=connection.prepareStatement(sql2);
            ps.setInt(1,cart);
            ps.setInt(2,cart);
            //2-3执行sql语句
            int row2=ps.executeUpdate();
            if(row1>0 && row2>0){
                connection.commit();
                return true;
            }

        }catch (Exception e){
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    /**
     * 删除购物车的商品
     * @param id
     * @return
     */
    public boolean deleteCartSelectedMerById(int id,int cart) {
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{
            connection.setAutoCommit(false);
            //2-1获得承接sql的容器对象
            String sql="delete  from cartselectedmer where id=?" ;
            //2-2获得sql语句
            PreparedStatement  ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            int row1=ps.executeUpdate();
            //更新购物车
            String sql2="update cart set money=(select sum(money) from cartselectedmer  where cart=? group by cart) where id=?";
            ps=connection.prepareStatement(sql2);
            ps.setInt(1,cart);
            ps.setInt(2,cart);
            //2-3执行sql语句
            int row2=ps.executeUpdate();
            if(row1>0 && row2>0){
                connection.commit();
                return true;
            }

        }catch (Exception e){
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } return false;
    }

    /**
     * 清空购物车
     * @param cart
     * @return
     */
    public boolean deleteCartSelectedMerByCart(Integer cart) {

        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{
            connection.setAutoCommit(false);
            //2-1获得承接sql的容器对象
            String sql="delete  from cartselectedmer where cart=?" ;
            //2-2获得sql语句
            PreparedStatement  ps=connection.prepareStatement(sql);
            ps.setInt(1,cart);
            int row1=ps.executeUpdate();
            //更新购物车
            String sql2="update cart set money=(select sum(money) from cartselectedmer  where cart=? group by cart) where id=?";
            ps=connection.prepareStatement(sql2);
            ps.setInt(1,cart);
            ps.setInt(2,cart);
            //2-3执行sql语句
            int row2=ps.executeUpdate();
            if(row1>0 && row2>0){
                connection.commit();
                return true;
            }

        }catch (Exception e){
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } return false;
    }


    public boolean updateCartSelectedMerNumById(int id,int num,int cart) {
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{
            connection.setAutoCommit(false);
            //2-1获得承接sql的容器对象
            String sql="update  cartselectedmer set number=?,money=price*?  where id=?" ;
            //2-2获得sql语句
            PreparedStatement  ps=connection.prepareStatement(sql);
            ps.setInt(1,num);
            ps.setInt(2,num);
            ps.setInt(3,id);
            int row1=ps.executeUpdate();
            //更新购物车
            String sql2="update cart set money=(select sum(money) from cartselectedmer  where cart=? group by cart) where id=?";
            ps=connection.prepareStatement(sql2);
            ps.setInt(1,cart);
            ps.setInt(2,cart);
            //2-3执行sql语句
            int row2=ps.executeUpdate();
            if(row1>0 && row2>0){
                connection.commit();
                return true;
            }

        }catch (Exception e){
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } return false;

    }

    public boolean submitShopCar(Integer cart, Integer member,String orderNo,String orderDate) {
        OrdersVo ordersVo=null;
        //1-获得数据库的连接
        Connection connection= ComboPooledDataSourceUtil.getConnection();
        //2-操作数据库表
        try{

            connection.setAutoCommit(false);
    //--1--将购物车的状态改为1
            String sql1="UPDATE cart set cartStatus=1 WHERE id=?";
            PreparedStatement ps=connection.prepareStatement(sql1);
            ps.setInt(1,cart);
            int row1=ps.executeUpdate();
            //---2---生成一个订单信息
            String sql2="insert into orders(member,cart,orderNo,orderDate,orderstatus) VALUES(?,?,?,?,1)";
            ps=connection.prepareStatement(sql2);
            ps.setInt(1,member);
            ps.setInt(2,cart);
            ps.setString(3,orderNo);
            ps.setString(4,orderDate);
            int row2=ps.executeUpdate();
        //--3-- 创建一个购物车
            String sql3="insert into cart(member,money,cartStatus) values(?,?,?)";
            ps=connection.prepareStatement(sql3);
            ps.setInt(1,member);
            ps.setBigDecimal(2,new BigDecimal("0.00"));
            ps.setInt(3,0);
            int row3=ps.executeUpdate();
            if(row1>0&&row2>0&&row3>0){
                connection.commit();
                return true;
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //3关闭数据库连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
}
